#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @File  : sqlHelper.py
# @Author: Xuhao
# @Date  : 2017/12/4
# @Desc  :

import pymysql.cursors
from datetime import datetime

#MYSQL_HOSTS = '47.94.105.135'
MYSQL_HOSTS = '127.0.0.1'
MYSQL_USER = 'root'
MYSQL_PASSWORD = 'MySqlXuhao'
MYSQL_PORT = '3306'
MYSQL_DB = 'HiXiaoShuo'

# MYSQL_HOSTS = 'localhost'
# MYSQL_USER = 'root'
# MYSQL_PASSWORD = '123456'
# MYSQL_PORT = '3306'
# MYSQL_DB = 'HiXiaoShuo'

# 打开数据库连接

class Sqlhelper:


    def close(self):
        self.db.close

    def __init__(self):
        self.db = pymysql.connect(host=MYSQL_HOSTS,
                             user=MYSQL_USER,
                             password=MYSQL_PASSWORD,
                             db=MYSQL_DB,
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

    def fetchBookDetail(self, bookID):

        try:
            with self.db.cursor() as cursor:

                sql = "select * from NoverInfo where noveID='%s' " % (
                    bookID)
                print(sql)
                cursor.execute(sql)
                return cursor.fetchone()
        except Exception as e:
            print(e)
            print('查询错误')

            return None
        finally:
            self.db.close()

    def fetchBookList(self, categoryStr, startID, count):

        '''
        获取小说章节列表
        :param tableName: 小说ID
        :param chapterID: 当前章节ID
        :param chapterID: 获取章节数量
        :return:
        '''

        try:
            with self.db.cursor() as cursor:

                if categoryStr == "全部":
                    sql = "select  p.* from " \
                          "(select b.*,IFNULL((select clickCount from bookClickRankList where bookID=b.noveID ),0) " \
                          "as clickCount from NoverInfo as b where b.noveUrl is NOT NULL ) " \
                          "as p order by p.clickCount DESC limit %s,%s " % (startID, count)
                    # sql = "select * from NoverInfo where noveUrl is not null ORDER BY novelastUpdataTime DESC limit %s,%s " % (startID, count)
                else:
                    sql = "select  p.* from " \
                          "(select b.*,IFNULL((select clickCount from bookClickRankList where bookID=b.noveID ),0) " \
                          "as clickCount from NoverInfo as b where b.noveCategory='%s' and b.noveUrl is NOT NULL ) " \
                          "as p order by p.clickCount DESC limit %s,%s " % (categoryStr, startID, count)
                    # sql = "select * from NoverInfo where noveCategory='%s' and noveUrl is not null ORDER BY noveCategory asc limit %s,%s" % (
                    #     categoryStr,startID, count)
                print(sql)
                cursor.execute(sql)
                return cursor.fetchall()
        except Exception as e:
            print(e)
            print('查询错误')
            return None
        finally:
            self.db.close()


    def fetchChapterList(self, tableName, chapterID, count, isContent):

        '''
        获取小说章节列表
        :param tableName: 小说ID
        :param chapterID: 当前章节ID
        :param chapterID: 获取章节数量
        :return:
        '''
        try:
            with self.db.cursor() as cursor:

                if int(isContent) == 1:
                    sql = "select novelastUpdataTime,chapterName,chapterID,chapterContent from `%s`  where chapterID>=%s ORDER BY chapterID asc limit %s" % (tableName, chapterID, count)
                else:
                    sql = "select novelastUpdataTime,chapterName,chapterID from `%s`  where chapterID>=%s ORDER BY chapterID asc limit %s" % (tableName, chapterID, count)
                print(sql)
                cursor.execute(sql)
                return cursor.fetchall()
        except Exception as e:
            print(e)
            print('查询错误')
            return -1
        finally:
            self.db.close()



    def fetchChapterContent(self, tableName, chapterID, count=1):
        '''
        根据章节ID获取章节内容
        :param tableName:
        :param chapterID:
        :return:
        '''
        try:
            with self.db.cursor() as cursor:

                sql = "select chapterContent,novelastUpdataTime,chapterName,chapterID from `%s` where chapterID>%s ORDER BY chapterID asc limit %s" % (tableName, chapterID, count)
                cursor.execute(sql)
                print(sql)
                return cursor.fetchall()
        except Exception as e:
            print(e)
            print('查询错误')
            return -1
        finally:
            self.db.close()


    def addFeedBack(self, content, title):
        '''
        根据章节ID获取章节内容
        :param tableName:
        :param chapterID:
        :return:
        '''
        try:
            with self.db.cursor() as cursor:
                nowTime = datetime.now()

                sql = "INSERT INTO book_feedback (feedBackTitle,feedBackContent, feedBackTime) VALUES ('%s', '%s', '%s')" % (title,content, nowTime.strftime('%Y-%m-%d %H:%M:%S'))
                print(sql)
                cursor.execute(sql)
                self.db.commit()
                return True
        except Exception as e:
            print(e)
            print('查询错误')
            self.db.rollback()
            return False
        finally:
            self.db.close()


    def fetchFeedBack(self):

        try:
            with self.db.cursor() as cursor:

                sql = "select * from book_feedback"
                cursor.execute(sql)
                print(sql)
                return cursor.fetchall()
        except Exception as e:
            print(e)
            print('查询错误')
            return None
        finally:
            self.db.close()



    def findBook(self, keyName):

        try:
            with self.db.cursor() as cursor:

               # sql = "select * from NoverInfo WHERE novelName LIKE '%%%s%%'" % (keyName,)
                sql = "select * from NoverInfo WHERE novelName='%s'" % (keyName,)
                cursor.execute(sql)
                return cursor.fetchall()
        except Exception as e:
            print(e)
            print('查询错误')
            return None
        finally:
            self.db.close()


    def addHotsearchRank(self, bookName):
        try:
            with self.db.cursor() as cursor:

                sql = "INSERT INTO bookHotSearchRankList (`noveName`,count) VALUE ('%s',count) ON DUPLICATE KEY UPDATE count = count + 1" % bookName
                print(sql)
                cursor.execute(sql)
                self.db.commit()
        except Exception as e:
            print(e)
            print('插入错误')
            return None
        finally:
            self.db.close()


    def fetchHotSearchRankList(self,count):

        try:
            with self.db.cursor() as cursor:

                sql = "select noveName from bookHotSearchRankList  ORDER BY count DESC limit %s" % count
                cursor.execute(sql)
                return cursor.fetchall()
        except Exception as e:
            print(e)
            print('查询错误')
            return None
        finally:
            self.db.close()

    def fetchUUID(self, UUID, date):

        try:
            with self.db.cursor() as cursor:

                sql = "select cnt, UUID, date from bannerClick WHERE `UUID`='%s' and `date`='%s'" % (UUID, date)
               # print(sql)
                cursor.execute(sql)
                return cursor.fetchone()
        except Exception as e:
            print(e)
            print('查询错误')
            return None
        finally:
            self.db.close()

    def insertUUID(self, UUID, date, cnt):

        try:
            with self.db.cursor() as cursor:

                sql = "INSERT INTO bannerClick (`UUID`,`date`, `cnt`) VALUE ('%s','%s', '%s')" % (UUID, date, cnt)
                print(sql)
                cursor.execute(sql)
                self.db.commit()
        except Exception as e:
            print(e)
            print('查询错误')
            return None
        finally:
            self.db.close()

    def updataUUID(self, UUID, date, cnt):
        try:
            with self.db.cursor() as cursor:

                sql = "UPDATE bannerClick SET `cnt`=%s WHERE `UUID`='%s' AND `date`='%s'" % (cnt, UUID, date)
                print(sql)
                cursor.execute(sql)
                self.db.commit()
        except Exception as e:
            print(e)
            print('查询错误')
            return None
        finally:
            self.db.close()



